import sqlite3
from sqlite3 import Error


class SQLiteDatabase:
    def __init__(self, db_file):
        """
        初始化数据库连接
        :param db_file: 数据库文件路径
        """
        self.db_file = db_file
        self.conn = None
        self.cursor = None
        self.connect()

    def connect(self):
        """连接到 SQLite 数据库（如果数据库文件不存在则创建）"""
        try:
            self.conn = sqlite3.connect(self.db_file, check_same_thread=False)
            self.cursor = self.conn.cursor()
            print(f"Connected to SQLite database: {self.db_file}")
        except Error as e:
            print(f"Error while connecting to SQLite: {e}")
            raise

    def create_table(self, create_table_sql):
        """
        创建数据库表, 已存在则不会创建
        :param create_table_sql: 创建表的 SQL 语句
        """
        try:
            self.cursor.execute(create_table_sql)
            self.conn.commit()
            print("Table created successfully")
        except Error as e:
            print(f"Failed to create table: {e}")

    def insert_data(self, table, data):
        """
        插入数据到指定的表中
        :param table: 表名
        :param data: 字典形式，键为列名，值为列值
        return 返回插入数据的id值
        """
        columns = ", ".join(data.keys())
        placeholders = ", ".join(["?"] * len(data))
        values = tuple(data.values())

        sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"

        insert_id = -1
        try:
            self.cursor.execute(sql, values)
            self.conn.commit()
            print(f"Data inserted into {table} successfully")
            return self.cursor.lastrowid
        except Error as e:
            print(f"Failed to insert data: {e}")
            return insert_id

    def select_data(self, table, where=None, limit=None, offset=None):
        """
        查询数据
        :param table: 表名
        :param where: 查询条件（可选）
        :param limit: 每页显示的记录数（可选）
        :param offset: 数据偏移量（可选）
        :return: 查询结果（元组列表）
        """
        sql = f"SELECT * FROM {table}"
        if where:
            sql += f" WHERE {where}"

        # 添加分页支持
        if limit is not None:
            sql += f" LIMIT {limit}"
        if offset is not None:
            sql += f" OFFSET {offset}"
        print(sql)
        try:
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Error as e:
            print(f"Failed to fetch data: {e}")
            return None

    def runSql(self, sql):
        """
        运行其它sql
        """
        try:
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Error as e:
            print(f"Failed to fetch data: {e}")
            return None

    def update_data(self, table, updates, where):
        """
        更新表中的数据
        :param table: 表名
        :param updates: 要更新的列和值（字典形式）
        :param where: 更新条件（字符串）
        """
        set_clause = ", ".join([f"{key} = ?" for key in updates.keys()])
        values = tuple(updates.values())

        sql = f"UPDATE {table} SET {set_clause} WHERE {where}"

        try:
            self.cursor.execute(sql, values)
            self.conn.commit()
            print(f"Data updated in {table} successfully")
        except Error as e:
            print(f"Failed to update data: {e}")

    def delete_data(self, table, where):
        """
        删除表中的数据
        :param table: 表名
        :param where: 删除条件（字符串）
        """
        sql = f"DELETE FROM {table} WHERE {where}"

        try:
            self.cursor.execute(sql)
            self.conn.commit()
            print(f"Data deleted from {table} successfully")
        except Error as e:
            print(f"Failed to delete data: {e}")

    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            print("SQLite connection closed")


# 测试用例
if __name__ == "__main__":
    # 初始化数据库连接
    db = SQLiteDatabase("HGMSimDatabase.db")

    # 创建表
    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT NOT NULL
    )
    """
    db.create_table(create_table_query)

    # 插入数据
    user_data = {"name": "John Doe", "age": 30, "email": "john@example.com"}
    db.insert_data("users", user_data)

    # 查询数据
    results = db.select_data("users")
    for row in results:
        print(row)

    # 更新数据
    update_data = {"age": 31}
    db.update_data("users", update_data, "name = 'John Doe'")

    # 关闭连接
    db.close()
